Re: [SQL] Database Design question
От | Herouth Maoz |
---|---|
Тема | Re: [SQL] Database Design question |
Дата | |
Msg-id | l03130301b377f213dc2c@[147.233.159.109] обсуждение исходный текст |
Ответ на | Database Design question (Heiko Wilms <wilms@stud.fh-hannover.de>) |
Список | pgsql-sql |
At 09:39 +0300 on 31/05/1999, Heiko Wilms wrote: > trying to redisign a database with the ER-model, I now have some N:N > relations. > I solved this by using arrays in some cases but querying gets very > complex then. > What is a "common" way to treat N:N relations? You create a third table, which describes the relation. The classical example is a relation between students and courses. Each student can study in several courses, and there are also several students in each course. Thus you have: 1. Student table, which contains all data which is only student-related. (such as first and last name, status, etc.) 2. Course table, containing all course data (such as syllabus) 3. Student-course table, sometimes called "enrollment" table. It contains the key into the student table, the key into thecourse table, and perhaps extra details which relate to the specific enrollment, such as the student's current markin the given course. (Of course, this is a simplified schema. In real life you will have many additional tables, because there will be several marks per enrollment, and perhaps several instances of each course, and so on). So, if Mark and Merry study biology 101, Moses and same Mark study chem 102, and Molly studies computer science 101, you have: Students: ID Name .... 001 Mark .... 002 Merry .... 003 Moses .... 004 Molly .... Courses: ID Name .... 001 biology 101 .... 002 chem 102 .... 003 computer science 101 .... Enrollment: StID CourseID .... 001 001 002 001 003 002 001 002 004 003 Now, you ask yourself, how do I get all students who study biology 101? You use SELECT s.ID, s.Name FROM Students s, Courses c, Enrollment e WHERE c.Name = 'biology 101' AND c.ID = e.CourseID AND s.ID = e.StID; In the same manner, if you want to know all the courses to which Mark is enrolled, you use SELECT c.ID, c.Name FROM Students s, Courses c, Enrollment e WHERE s.Name = 'Mark' AND c.ID = e.CourseID AND s.ID = e.StID; One last note: for this to be efficient, you have to have indices on the proper fields. These will always be the primary keys in the two main tables (the course ID and the student ID), and the corresponding foreign keys in the relation table (StID and CourseID in enrollment). Additional indices may be needed if you intend to search by any other field. For example, if you intend to select by student name, as per the second example query above, you need to have an index on the Name field in Students as well. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma
В списке pgsql-sql по дате отправления: